package persistence.dao;

import java.io.Serializable;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashSet;
import java.util.Set;

import core.ModelObject;


import persistence.DbUtils;
import persistence.TransactionManager;
import persistence.dto.ProductGroupDTO;
import persistence.dto.StorageUnitDTO;

public class ProductGroupsDBDAO extends ModelObject implements IProductGroupsDAO
{

	
	/**
	 * 
	 */
	private static final long serialVersionUID = 8486025380003471462L;
	private static ProductGroupsDBDAO instance;
	
	public static ProductGroupsDBDAO getInstance()
	{
		if(instance == null)
		{
			instance = new ProductGroupsDBDAO();
		}
		return instance;
	}
	
	private ProductGroupsDBDAO()
	{
		
	}
	
	/*CREATE TABLE ProductContainers 
	(
			ID INTEGER PRIMARY KEY  NOT NULL  UNIQUE, 
			name varchar(255) NOT NULL, 
			containerType varchar(15) NOT NULL, 
			parentID INTEGER, 
			threeMonthNum FLOAT, 
			threeMonthType varchar(12)
			constraint amt_ck check (threeMonthType in ('count','pounds'
			'ounces','grams','kilograms',
			'gallons','quarts','pints','fluidOunces','liters'))
			constraint cont_type_ck check (containerType in ('storage unit','product container'))
		);
		*/
	
	
	@Override
	public Set<ProductGroupDTO> getAllChildrenProductGroups(int parentID)
	{
		PreparedStatement stmt = null;
		ResultSet rs = null;

		Set<ProductGroupDTO> allPGs = new HashSet<ProductGroupDTO>();
		try {
			String query = "select id, name, containerType, parentID, threeMonthNum,"+
			" threeMonthType from ProductContainers" +
					" where parentID = ?";
			stmt = TransactionManager.getConnection().prepareStatement(query);
			stmt.setInt(1, parentID);
			

			rs = stmt.executeQuery();
			while (rs.next()) 
			{
				int id = rs.getInt(1);
				String name = rs.getString(2);
				String containerType = rs.getString(3);
				int parentId = rs.getInt(4);
				float threeMonthNum = rs.getFloat(5);
				String threeMonthType = rs.getString(6);
				//(int id, String name, int parentID, float threeMonthNum, String threeMonthType)
				allPGs.add(new ProductGroupDTO(id, name, parentId, threeMonthNum, threeMonthType));
			}
			
		}
		catch (SQLException e) {
			System.out.println("SQL Error in ProductGroupsDBDAO: " + e.getMessage());
		}		
		finally {
			DbUtils.safeClose(rs);
			DbUtils.safeClose(stmt);
		}
	
		return allPGs;
	}

	@Override
	public int add(ProductGroupDTO productGroupDTO)
	{
		int result = 0;
		Statement keyStmt = null;
		ResultSet keyRS = null;
		PreparedStatement stmt = null;
		if(productGroupDTO.getId() != 0)
			return productGroupDTO.getId();
		try {
			String query = "insert into ProductContainers (name, containerType, parentId,"+
			" threeMonthNum, threeMonthType)" +
					" values (?, ?, ?, ?, ?)";
			stmt = TransactionManager.getConnection().prepareStatement(query);
			stmt.setString(1, productGroupDTO.getName());
			stmt.setString(2, productGroupDTO.getType());
			stmt.setInt(3, productGroupDTO.getParentID());
			stmt.setFloat(4, productGroupDTO.getThreeMonthNum());
			stmt.setString(5, productGroupDTO.getThreeMonthType());
			if (stmt.executeUpdate() == 1)
			{
				keyStmt = TransactionManager.getConnection().createStatement();
				keyRS = keyStmt.executeQuery("select last_insert_rowid()");
				keyRS.next();
				int id = keyRS.getInt(1); // ID of the new ProductGroup
				try{
					if(keyStmt!=null)keyStmt.close();
				}
				catch(SQLException e)
				{
					
				}
				return id;
						
			}
			else
				System.out.println("Could not insert ProductContainer");
			DbUtils.safeClose(stmt);
		}
		catch (SQLException e) {
			System.out.println("SQLException in add ProductContainer DB: " + e.getMessage());
		}
		finally
		{ 
			
			DbUtils.safeClose(stmt);
			DbUtils.safeClose(keyRS);
			
		}
		
		return 0;	
	}

	@Override
	public boolean edit(ProductGroupDTO productGroupDTO)
	{
		PreparedStatement stmt = null;
		try {
			String query = "update ProductContainers set name = ?, containerType = ?, "+
			"parentID = ?," +
					" threeMonthNum = ?, threeMonthType = ? where ID = ?";
			stmt = TransactionManager.getConnection().prepareStatement(query);
			stmt.setString(1, productGroupDTO.getName());
			stmt.setString(2, productGroupDTO.getType());
			stmt.setInt(3, productGroupDTO.getParentID());
			stmt.setFloat(4, productGroupDTO.getThreeMonthNum());
			stmt.setString(5, productGroupDTO.getThreeMonthType());
			stmt.setInt(6, productGroupDTO.getId());
			if (stmt.executeUpdate() == 1)
			{
				//keyStmt = TransactionManager.getConnection().createStatement();
				//keyRS = keyStmt.executeQuery("select last_insert_rowid()");
				//keyRS.next();
				//int id = keyRS.getInt(1); // ID of the new ProductGroup
				//return id;
				return true;
			}
			else
			{
				System.out.println("Could not update Product Group");
				return false;
			}
			
		}
		catch (SQLException e) {
			System.out.println("edit Product Group DTO error: " + e.getMessage());
		}
		finally
		{
			DbUtils.safeClose(stmt);
		}
		
		return false;	
	}

	@Override
	public boolean remove(ProductGroupDTO productGroupDTO)
	{
		PreparedStatement stmt = null;
		try {
			String query = "delete from ProductContainers where id = ? or parentID = ?";
			stmt = TransactionManager.getConnection().prepareStatement(query);
			stmt.setInt(1, productGroupDTO.getId());
			stmt.setInt(2, productGroupDTO.getId());
			if (stmt.executeUpdate() == 1)
				return true;
			else
				System.out.println("Could not delete Product Group");
			return false;
		}
		catch (SQLException e) {
			System.out.println("remove ProductGroup DAO failed: " + e.getMessage());
			e.printStackTrace();
		}
		finally
		{
			DbUtils.safeClose(stmt);
		}
		
		return false;
	}

}
